<?php
namespace App\Models;

use DB;
use App\Models\TagSelfM;
use App\Models\IconM;
use Illuminate\Database\Eloquent\Model;

/** 为公开 **/
class IconSelfM extends Model
{
    protected $table='lee_icon_self';
    static public $STable='lee_icon_self';
    
    // set table's primary key to UUID
    public $incrementing=false;
    public $primaryKey='uuid';
    

    /**
     * 添加数据记录到 icon 数据表
     * @param array $data 待添加数据
     * 如 array('userid'=>userid,'description'=>discription,'id'=>iconid)
     */
    static public function addItem(array $data){
        $iconInst=new IconSelfM();
        foreach ($data as $key => $value){
            $iconInst->$key=$value;
        }
        $iconInst->save();
    }
    
    
    /**
     * 依据 icon的fid 返回其 id
     * @param bigint $iconfid
     */
    static public function getID($iconfid){
        $iconInst= self::where('fid','=',$iconfid)->first();
        return ['icnid'=>$iconInst->id,'userid'=>$iconInst->userid];
    }
    
    /**
     * 依据uuid删除单个数据记录
     * @param uuid $id,待删除数据的主键
     */
    static public function deltItem($id){
        IconSelfM::destroy($id);
    }
    
    /**
     * 依据icon的fid删除单个数据记录
     * @param bigint $icnfid,待删除icon的fid
     */
    static public function deltItembyFid($icnfid){
        IconSelfM::where('fid','=',$icnfid)->delete();
    }
    
   
    /**
     * 加载 所有icon及其相关的数据
     * @param bigint $userid 用户id
     * 返回项包括：
     *  icon的 fid,title,content,beliked(被点赞数),bestored（被收藏数）,
     *  lng（经度）,lat（纬度）,address（地点）,createdat(创建时间)
     *  favourstate（是否已被该用户点赞）,storedstate（是否已被该用户收藏）
     *  user的 id,name,avatar
     *  image的fid,path
     *
     */
    static public function loadAllSelfIcons($userid){
        $icnTable=self::$STable;
        $imgTable=ImageSelfM::$STable;

        $sql="select icn.fid as icnfid, icn.title as title,icn.description as content,
        icn.lng as lng,icn.lat as lat,
        icn.address as address,icn.created_at as createdat,
        
        img.fid as imgfid,img.path as imglink ,img.width as imgWd ,img.height as imgHt
        
        FROM $icnTable as icn
        
        LEFT OUTER JOIN $imgTable as img
        ON icn.id=img.iconid
        
        where icn.userid=$userid";
        return DB::select($sql);
    }
    
    
  
    
    
    /**
     * 依据 用户id和日期 返回 当月上传日 iconfid的集合
     * @param unknown $userid
     * @param unknown $date
     */
    static public function loadIconInMonth($userid,$date){
        $icnTable=self::$STable;
        $sql="select extract(day from created_at) as cdate,icn.fid as iconfid from $icnTable as icn
        where userid = $userid
        and extract(month from created_at) = extract(month from date('$date'))
        and extract(year from created_at) = extract(year from date('$date'))";
        return DB::select($sql);
    }
      
    
    /**
     * 依据 icon的fid 返回和该fid有关的 image表id(uuid)、存储路径 
     * @param bigint $icnfid, icon的fid
     * @return 数组  [ 'imgids'=>[img1,img2],'imgpaths'=>[path1,path2],'cmtids'=>[cmt1,cmt2] ]
     */
    static  public function getImgAndCmtIDs($icnfid){
        $imgT=ImageSelfM::$STable;
        $icnT=self::$STable;
        $sql="select img.id as imgids,img.path as imgpath
        from $imgT as img
        where img.iconid=
        (select icn.id from $icnT as icn where icn.fid=$icnfid)";      
        $sqlR=DB::select($sql);
        return ['imgids'=>array_unique(array_column($sqlR, 'imgids')),
            'imgpaths'=>array_unique(array_column($sqlR, 'imgpath'))
        ];
    }
    
    /**
     * 发布 icon数据
     * @param unknown $iconfid
     */
    static public function publishIcon($iconfid){
        $slfIconT=self::$STable;
        $IcnT=IconM::$STable;
        
        $sql="insert into $IcnT 
            (userid,created_at,updated_at,description,id,
                fashion_style,title,lng,lat,address) 
             select icn.userid,now(),now(),icn.description,icn.id,
                icn.fashion_style,icn.title,icn.lng,icn.lat,icn.address
            from $slfIconT icn
            where icn.fid=$iconfid";
        DB::select($sql);
        
    }
    
}

?>